﻿using Dapper;
using Estsh.Core.Base;
using Estsh.Core.Dapper;
using Estsh.Core.Wms.IRepositories;
using Estsh.Core.Model.Result;
using Estsh.Core.Repositories;
using System.Collections;
using System.Data;
using System.Text;
using System.Security.Cryptography;
using System.Text.Json;
using Estsh.Core.Models;
using Estsh.Core.Model.Extend.Models;
using Newtonsoft.Json.Linq;
using Estsh.Core.Model.EnumUtil;
using Estsh.Core.Model.Extend;

namespace Estsh.Core.Wms.Repositories
{
    public class StockRepository : BaseRepository<SysStock_Extend>, IStockRepository
    {
        public StockRepository(DapperDbContext _dapperDbContext) : base(_dapperDbContext)
        {

        }

        /// <summary>
        /// 获取箱条码信息
        /// </summary>
        /// <param name="cartonNo"></param>
        /// <returns></returns>
        public SysStock_Extend GetCartonInfo(string cartonNo)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                string result = string.Empty;

                StringBuilder sql = new StringBuilder(1024);
				// sql.Append("select * from sys_stock (nolock) where carton_no=@carton_no ");
				sql.Append("SELECT b.enum_name AS stock_status,* FROM  dbo.sys_stock (NOLOCK) a LEFT JOIN dbo.sys_enum b ON b.enum_type='sys_stock_status' WHERE  a.carton_no=@carton_no ");
				DynamicParameters parameters = new DynamicParameters();
                parameters.Add("@carton_no", cartonNo);

                //List<SysStock> dt = dbConn.Query<SysStock>(sql.ToString(), parameters).ToList();
                return dbConn.QueryFirstOrDefault<SysStock_Extend>(sql.ToString(), parameters);
            }
        }

        /// <summary>
        /// 获取库位对应的库存信息
        /// </summary>
        /// <param name="locateName"></param>
        /// <returns></returns>
        public List<SysStock> GetLocateStockInfo(string locateName)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                string result = string.Empty;

                StringBuilder sql = new StringBuilder(1024);
                sql.Append("SELECT * FROM dbo.sys_stock (nolock) WHERE enabled = 'Y' AND locate_name = @locateName  ");
                DynamicParameters parameters = new DynamicParameters();
                parameters.Add("@locateName", locateName);

                List<SysStock> stocks = dbConn.Query<SysStock>(sql.ToString(), parameters).ToList();
                return stocks;
            }
        }

		/// <summary>
		/// 根据库位获取条码信息
		/// </summary>
		/// <param name="locateName"></param>
		/// <returns></returns>
		public List<SysStock> GetStockQtyByLocate(string locateName)
		{
			using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
			{
				dbConn.Open();

				StringBuilder stringBuilder = new StringBuilder();
				//sql逻辑：查询库存条码表， 判断当前库位的条码数量，条件为条码的状态为 50：已上架、60：已配料、80：NC隔离 标识为Y
				stringBuilder.Append("  SELECT SUM(qty) as qty FROM dbo.sys_stock (nolock) WHERE locate_name = '" + locateName + "' AND status IN ('50','60','80') and enabled='Y' ");
				List<SysStock> sysStocksLocateCount = dbConn.Query<SysStock>(stringBuilder.ToString()).ToList();//库位在库数
				return sysStocksLocateCount;
			}
		}

		/// <summary>
		/// 获取库存数量（根据零件+库区）
		/// </summary>
		/// <param name="partId"></param>
		/// <param name="zoneId"></param>
		/// <returns></returns>
		public decimal GetStockQty(int partId,int zoneId)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();

                StringBuilder sql = new StringBuilder(1024);
                sql.Append("SELECT SUM(qty) FROM dbo.sys_stock (nolock) ");
                sql.Append("WHERE enabled = 'Y' AND part_id = @partId AND zone_id = @zoneId ");

                DynamicParameters parameters = new DynamicParameters();
                parameters.Add("@partId", partId);
                parameters.Add("@zoneId", zoneId);
                
                var result = dbConn.ExecuteScalar(sql.ToString(), parameters);

                if (result == null)
                {
                    return 0;
                }
                else
                { 
                    return decimal.Parse(result.ToString());
                }
            }
        }
    }
}
